﻿----------------数据查询----------------
--AdventureWorks示例数据库
USE AdventureWorks
GO

--------------SELECT子句----------------
--Select一个或多个常量
select 1
select 'select 1 '
select 'this is a character constant', 'this is another character constant', 1, '7/27/2008'
GO

--Select *返回全部字段,但编程时最好不要使用*,而使用具体的字段名
SELECT *
FROM Person.Address
GO

--从多张表查询数据时，字段名前要加上表名
SELECT Person.Address.AddressID, Person.Address.AddressLine1, Person.Address.AddressLine2,
Person.Address.City, Person.Address.StateProvinceID, Person.Address.PostalCode
FROM Person.Address
GO


----------------列的别名--------------------
--T-SQL可以对表和列重命名,有3种格式：
--<alias> = <column>
--<column/table> AS <alias>
--<column/table> <alias>

--第1种：
SELECT MyAddress=AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address

--第2种：
SELECT AddressID AS MyAddress, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address

--第3种：
SELECT AddressID MyAddress, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address

--第3种用于表名：
SELECT a.AddressID MyAddress, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID,
a.PostalCode
FROM Person.Address a
GO


-----------------SQL函数------------------
--SQL有100多个函数,可用于汇总，分析和操纵数据

-----------CAST AS语句和CONVERT函数---------
--使用CAST AS语句或CONVERT函数转换返回的数据类型
--最好像varchar(30)这样指定长度为30
SELECT CAST(AddressID AS varchar(30)), AddressLine1, AddressLine2, City, StateProvinceID
FROM Person.Address

--不指定长度时使用默认长度,不推荐这样
SELECT CAST(AddressID AS varchar), AddressLine1, AddressLine2, City, StateProvinceID
FROM Person.Address

--使用Convert函数转换
SELECT CONVERT(varchar(30),AddressID), AddressLine1, AddressLine2, City, StateProvinceID
FROM Person.Address
GO

--Convert有多个格式参数
--转换为MM/DD/YYYY
SELECT CONVERT(varchar(30),ModifiedDate,101) AS MyDate, AddressLine1
FROM Person.Address
--转换为DD/MM/YYYY
SELECT CONVERT(varchar(30),ModifiedDate,103) AS MyDate, AddressLine1
FROM Person.Address
--转换为MM/DD/YY
SELECT CONVERT(varchar(30),ModifiedDate,1) AS MyDate, AddressLine1
FROM Person.Address
--转换为DD/MM/YY
SELECT CONVERT(varchar(30),ModifiedDate,3) AS MyDate, AddressLine1
FROM Person.Address
GO


------------ISNULL和COALESCE函数----------
--使用+号连接两个字段，ISNULL和COALESCE函数能避免字段为空时相加结果为空
SELECT AddressID, AddressLine1 + ' ' + ISNULL(AddressLine2,''), City, StateProvinceID
FROM Person.Address

SELECT AddressID, AddressLine1 + ' ' + COALESCE(AddressLine2,''), City, StateProvinceID
FROM Person.Address

SELECT ProductID, ISNULL(Color,'No Color')
FROM Production.Product
GO


-------------Count函数统计行数-------------
SELECT COUNT(*) FROM Person.Address
GO


-----------------MAX函数----------------
--查询字段中值的最大长度
SELECT MAX(DATALENGTH(AddressLine1))
FROM Person.Address
GO


-----------CASE...WHEN...ELSE-----------
--有条件地返回结果集中的值
SELECT ShipMethodID, CASE ShipMethodID 
                WHEN 1 THEN 'A.Datum'
                WHEN 2 THEN 'Contoso'
                WHEN 3 THEN 'Consolidated Messenger'
                ELSE 'Unknown'
                END
FROM Purchasing.PurchaseOrderHeader
GO


-----------ORDER BY子句对结果排序---------
--可以在ORDER BY子句里指定一列或多列,但所有列总长度不能超过8060字节
--ORDER BY子句里不能有TEXT,NTEXT,IMAGE或XML数据类型的列
--ORDER BY子句里的列不必在SELECT语句中
--默认为升序排列ASC,也可指定DESC则为降序排列

SELECT AddressID, AddressLine1 + ' ' + ISNULL(AddressLine2,''), City, StateProvinceID
FROM Person.Address
ORDER BY PostalCode DESC, AddressID
GO


-----------WHERE子句:数据筛选-------------
SELECT * FROM Person.Address WHERE AddressID = 102

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE ListPrice < 15

--用 AND 和 OR 设定复合条件
SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE ListPrice > 8.99 AND ListPrice < 34.99

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE ListPrice > 8.99 OR ListPrice < 34.99
GO

--BETWEEN...AND相当于>=(min) AND <=(max)
--下面2条语句等价
SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE ListPrice >= 8.99 AND ListPrice <= 34.99
GO

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE ListPrice BETWEEN 8.99 and 34.99 

--可以多个条件组合
SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE (ListPrice BETWEEN 2 AND 15)
    OR (ListPrice BETWEEN 18 AND 50)

--IN关键字
--T-SQL不支持数组,但是可以用IN关键字指定一个值列表
SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE ProductSubCategoryID IN (1,2,3)

--LIKE关键字+通配符搜索字符片段
--通配符有两种：%表示任意多个字符, _表示单个字符
--通配符搜索以散列值开头时，会借助列上的索引来搜索(如果有的话)

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE Name LIKE 'Mountain%'

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE Name LIKE 'Mountain__0%'

--下面这张方式不是以离散值开头,无法使用索引,效率极低,尽量不使用
SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE Name LIKE '%ountain__0%'

--查找NULL值时应该使用IS NULL或IS NOT NULL,不能使用=NULL，=不能返回任何值
SELECT ProductID, Name, Color, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE Color IS NULL

SELECT ProductID, Name, Color, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE Color IS NOT NULL
GO

--TOP关键字，返回前N行数据
--返回前10行
SELECT TOP 10 ProductID, Name, Color, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product

--返回升序排列后的前10行
SELECT TOP 10 ProductID, Name, Color, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
ORDER BY Color

--返回降序排列后的前10行
SELECT TOP 10 ProductID, Name, Color, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
ORDER BY Color DESC
GO

--PERCENT关键字,返回前10%记录
SELECT TOP 10 PERCENT ProductID, Name, Color, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product


----------------------多表联接查询---------------------
--使用JOIN关键字，有5中联接操作：
--INNER JOIN， LEFT OUTER JOIN， RIGHT OUTER JOIN， FULL OUTER JOIN， CROSS JOIN 

--INNER JOIN返回两个表中完全匹配的行
SELECT a.FirstName, a.LastName, b.Title
FROM Person.Contact a INNER JOIN HumanResources.Employee b on a.ContactID = b.ContactID
GO

--ON子句附加搜索条件
SELECT a.ProductID, a.Name, b.SalesOrderID
FROM Production.Product a INNER JOIN Sales.SalesOrderDetail b 
        ON a.ProductID = b.ProductID AND a.Color = 'Black'

--最好使用WHERE附加条件筛选
SELECT a.ProductID, a.Name, b.SalesOrderID
FROM Production.Product a INNER JOIN Sales.SalesOrderDetail b 
        ON a.ProductID = b.ProductID 
WHERE a.Color = 'Black'
GO

--LEFT OUTER JOIN 左外联接：返回左表的所有记录，追加右表中匹配的记录
SELECT a.ProductID, a.Name, b.SalesOrderID
FROM Production.Product a LEFT OUTER JOIN Sales.SalesOrderDetail b 
        ON a.ProductID = b.ProductID
WHERE a.Color = 'Black'
ORDER BY 1

--RIGHT OUTER JOIN 右外联接：返回右表的所有记录，追加左表中匹配的记录
SELECT a.ProductID, a.Name, b.SalesOrderID
FROM Sales.SalesOrderDetail b RIGHT OUTER JOIN Production.Product a 
        ON a.ProductID = b.ProductID
WHERE a.Color = 'Black'
ORDER BY 1
GO

--下面两条语句等价，但是使用内联接比使用SELECT子句有更好的可读性
SELECT a.FirstName, a.LastName, b.Title
FROM Person.Contact a LEFT OUTER JOIN HumanResources.Employee b on a.ContactID = b.ContactID

SELECT a.FirstName, a.LastName,
    (SELECT e.Title
        FROM HumanResources.Employee e
        WHERE a.ContactID = e.ContactID) AS Title
FROM Person.Contact a
GO

--FULL OUTER JOIN全外联接，保留2个表的内容,不匹配的结果显示为NULL
SELECT a.ProductID, a.Name, b.SalesOrderID
FROM Production.Product a FULL OUTER JOIN Sales.SalesOrderDetail b 
        ON a.ProductID = b.ProductID
WHERE a.Color = 'Black'
ORDER BY 1
GO

--CROSS JOIN交叉表联接，返回左表的每一行分别与右表的每一行结合的结果集
--CROSS JOIN一般是数据库设计不合理时才会使用，正确的方法是修改数据库设计
SELECT a.FirstName, a.LastName, b.Title
FROM Person.Contact a CROSS JOIN HumanResources.Employee b
GO

--自联接
SELECT a.EmployeeID,c.FirstName,c.LastName,a.Title EmployeeTitle,d.FirstName,d.LastName,b.ManagerID, b.Title ManagerTitle
FROM HumanResources.Employee a INNER JOIN HumanResources.Employee b
ON a.ManagerID = b.EmployeeID
INNER JOIN Person.Contact c on a.ContactID = c.ContactID
INNER JOIN Person.Contact d on b.ContactID = d.ContactID
ORDER BY ManagerID

--非等值联接
SELECT b.ProductID, b.Name, b.ListPrice, a.UnitPrice AS 'Sales Price'
FROM Sales.SalesOrderDetail AS a
    JOIN Production.Product AS b 
    ON a.ProductID = b.ProductID AND a.UnitPrice < b.ListPrice
WHERE b.ProductID = 718

SELECT p1.VendorID, p1.ProductID
FROM Purchasing.ProductVendor p1
    INNER JOIN Purchasing.ProductVendor p2
    ON p1.ProductID = p2.ProductID
WHERE p1.VendorID <> p2.VendorID
ORDER BY p1.ProductID
GO


-----------------子查询---------------
--普通的子查询,下面的查询返回清单价大于平均价的产品
SELECT a.ProductID, a.Name, a.ListPrice
FROM Production.Product a
WHERE a.ListPrice > (SELECT AVG(b.ListPrice) FROM Production.Product b)
GO

--关联子查询，内联查询依赖外联查询的值，造成的结果是根据外联查询的输入重复地执行内联查询
--下面EXIST参数提高了查询性能,只要子查询的值满足一次即可
SELECT a.ProductID, a.ListPrice
FROM Production.Product a
WHERE EXISTS (SELECT 1 FROM Sales.SalesOrderDetail b 
                WHERE b.ProductID = a.ProductID)
GO

--DISTINCT关键字删除结果集中的重复值
SELECT DISTINCT b.ProductID, b.Name, b.ListPrice, a.UnitPrice AS 'Sales Price'
FROM Sales.SalesOrderDetail AS a
    JOIN Production.Product AS b 
    ON a.ProductID = b.ProductID AND a.UnitPrice < b.ListPrice
WHERE b.ProductID = 718

--DISTINCT与COUNT结合
--返回职位是经理的雇员的数量,注意DISTINCT放在COUNT之后
SELECT COUNT(DISTINCT ManagerID) FROM HumanResources.Employee